|
Deleting Rows
Below
are 6 methods that will delete rows from within a selection. If you
know the range you could replace "Selection" with Range(<your
range>)
In
some examples we turn off Calculation and Screenupdating. The reason
we turn off calculation is in case the range in which we are
deleting rows contains lots of formulas, if it does Excel
may
need to recalculate each time a row is deleted, slowing down the
macro. The screenupdating being set to false will also speed up our
macro as
Excel will not try to repaint the screen each time it changes. Subs:
DeleteBlankRows1,
DeleteBlankRows3 and
both
Worksheet_Change events are slightly different as they first check to see if the ENTIRE row is blank. Sub
DeleteBlankRows1()
'Deletes the entire row within the selection if
_
the
ENTIRE row contains no data.
Dim i As
Long 'We use
Long in case they have over _
32,767
rows selected
'we
turn off calculation and screenupdating to speed
_
up the
macro
With
Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
'We
work backwards because we are deleting rows.
For i =
Selection.Rows.Count To
1
Step
-1
If
WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End
If
Next
i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End
With
End
Sub
Sub
DeleteBlankRows2()
'Deletes the entire row within the selection if
_
the cells WITHIN THE SELECTION contains no data.
Selection.SpecialCells(xlBlanks).Delete
End
Sub
Sub
DeleteBlankRows3()
'Deletes the entire row within the selection if
_
the
ENTIRE row contains no data.
Dim Rw As
Range
myTime
= Time
With
Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
Selection.SpecialCells(xlCellTypeBlanks).Select
For
Each Rw
In
Selection.Rows
If
WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
Selection.EntireRow.Delete
End
If
Next
Rw
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End
Sub
Sub
MoveBlankRowsToBottom()
'Assumes the list has a heading
With
Selection
.Sort Key1:=.Cells(2, 1), Order1:=xlAscending,
_
Header:=xlYes, OrderCustom:=1,
MatchCase:=False,
_
Orientation:=xlTopToBottom
End
With
End
Sub
Sub
DeleteRowsBasedOnCriteria()
'Assumes the list has a heading
With
ActiveSheet
'If
filters are not visible then turn them on
If
.AutoFilterMode = False
Then
.Cells(1, 1).AutoFilter
'Set
the filters in A1 to show only rows to delete
.Cells(1, 1).AutoFilter Field:=1,
Criteria1:="Delete"
'Delete all visible cells under the
heading.
.Cells(1, 1).CurrentRegion.Offset(1,
0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
'Remove filters
.AutoFilterMode = False
End
With
End
Sub
To use
any or all of the above code:
1)
Open Excel.
2)
Push Alt+F11 to
open the VBE
(Visual Basic Editor).
3) Go
to Insert>Module.
4)
Copy the code and paste it in the new module.
5)
Push Alt+Q to
return to Excels normal view.
6)
Push Alt+F8 and
then select the macro name and click Run.Or
select Options and
assign a shortcut key.
Removing Blank
Automatically
The
codes above will work fine for removing blank rows from a list that
already has some, but as the saying goes "Prevention is better than cure". The
two examples below will remove blank rows as they occur. Either code
should be placed within the Worksheet module and will occur each
time a cell changes on the worksheet.
In
both codes you will notice the Application.EnableEvents=False
this is often needed within Event codes like this, else the Event will be triggered again once the code executes which in turn will again
trigger the Event and so on.....
You
will no doubt also notice the GoTo
SelectionCode which
occurs if the number of cells within the selection exceeds one. The
reason for this is an error would occur if the code reached the
Target
keyword as Target
refers to a single cell.
The
second example uses the Sort
method rather than the
EntireRow.Delete and is the preffered method to use if possible. What happens is, any blank rows are placed at the bottom of the range should the entire row be blank. The
use of the keyword Me
is a
good habit to get into when working within Worksheet and Workbook
modules. This was shown to me by my internet friend from Belgium,
Geert
Dumortier.
Private Sub
Worksheet_Change(ByVal
Target As
Excel.Range)
'Deletes blank rows as they occur
'Prevents endless loops
Application.EnableEvents = False
'They
have more than one cell selected
If
Target.Cells.Count > 1 Then
GoTo
SelectionCode
if
WorksheetFunction.CountA(Target.EntireRow) = 0 Then
Target.EntireRow.Delete
End If
Application.EnableEvents = True
Exit
Sub
Our
code will only enter here if the selection is _
more
than one cell.
SelectionCode:
If WorksheetFunction.CountA(Selection.EntireRow) = 0
Then
Selection.EntireRow.Delete
End If
Application.EnableEvents = True
End
Sub
Private Sub
Worksheet_Change(ByVal
Target As
Excel.Range)
'Sorts
blank rows to the bottom as they occur
'Prevents endless loops
Application.EnableEvents = False
'They
have more than one cell selected
If
Target.Cells.Count > 1 Then
GoTo
SelectionCode
If
WorksheetFunction.CountA(Target.EntireRow) = 0 Then
Me.UsedRange.Sort Key1:=[A1], Order1:=xlAscending
End
If
Application.EnableEvents = True
Exit
Sub
'Our
code will only enter here if the selection is _
more
than one cell.
SelectionCode:
If
WorksheetFunction.CountA(Selection.EntireRow) = 0 Then
Me.UsedRange.Sort Key1:=[A1], Order1:=xlAscending
End
If
Application.EnableEvents = True
End
Sub
'
To use
either one of the above codes:
1)
Open Excel.
2)
Right click on the Sheet name tab.
3)
Select View
Code from
the Pop-up menu
4)
Copy the code and paste it over the top of the default
Event
5)
Push Alt+Q to return to Excels normal view.
6)
Push Alt+F8 and then select the macro name and click Run.Or
select Options and assign a shortcut
key. |
|
drhawley@geo.net.au |
|
Of all
the examples above that use Excels
AutoFilters and Sort are by far the quickest methods I know of. If anybody knows of a quicker way, please tell me. |
|
Coming Soon.
OzGrid Extra Add-in
Please Contact Me
For Details |
|
Excel and VBA
Training and Tutoring
Spreadsheets,
Macros and Add-ins etc Help
Excel Formulas,
Functions, Charts.
Excel Graphs,
Code, Tips and Tricks etc
Microsoft Excel
Consultancy
Excel Automation
and Customization |
